Configuring: SQL Expressions

New fields will be created by the SQL expressions configured. You can enter as many new fields as space in the dataset allows. You can add up until the maximum number of fields allowed in a dataset or the maximum allowed SQL string length.

Worked example: SQL expression

Steps to configure

Enter expressions: Enter a name and type for the new fields that will be created when configuring the required SQL expressions.
Execute: The dataset is modified to include the new fields added by the SQL queries.

Step 1: Enter expressions

Enter a field name, field type and the defined expression required for the new field/s.

Field name and type:

The user must enter a valid field name.
The user must select the correct field type for the output expression from the given drop down list of data types.
Default:
- Field name: left blank. Spaces between words will be automatically corrected with an underscore (_) when you click out of the text box.
- Field type: the first field type listed is used.

Expressions:

Multiple expressions can be added.
Added expressions can be removed.
Use the fields listed in the Fields tab in the window on the right for the expression. Double click the field, or click the [<] button, to add the field to the Expressions text box on the left, to be used in the expression of the update query.
When writing an expression, use the functions given in the Functions tab in the window on the right. Double click the function, or use the [<] button, to add it to the Expressions text box on the left to be used in the expression.
- When a function is added to the Expressions text box, it already contains the opening parenthesis " ( ". Complete the expression and remember to close the parentheses.
Alternatively, write your own function in the Expression text box, given that it is a valid SQL function.
The expression can only be added if it is valid and the syntax is correct. This is checked when you [Add] the expression.
Default: a blank expression and a list of available fields and functions.
Minimum configuration: create at least one SQL expression by entering the new field name, field type and the expression.

SQL query:

For every expression field that is entered by the user, this query will be used:

UPDATE

[TableName]

SET

[FieldName(selected by the user)] = [Expression(selected or configured by the user)]

Step 2: Execute

The dataset is modified to contain the new fields with the user-defined values produced from the SQL queries.